local s = require "service"

local skynet = require "skynet"
local mysql = require "skynet.db.mysql"
local socketchannel = require "skynet.socketchannel"
local cjson = require "cjson"

local db
local mysql_host = "127.0.0.1"
local mysql_port = 3306
local mysql_database = "skynetdemo"
local mysql_username = "root"
local mysql_password = "123456"

s.resp.query_player_info = function(source, usnername, password)
    local sql = string.format("select * from users where username=%s and password=%s", mysql.quote_sql_str(usnername),
        mysql.quote_sql_str(password))

    skynet.error("query sql:", sql)
    local res = db:query(sql)

    skynet.error("query_player_info query ret:", cjson.encode(res))
    if #res == 0 then
        return false, res
    end

    return true, res[1]
end

s.resp.query_player_by_id = function(source, id)
    local sql = string.format("select username, exp, created, updated from users where id=%d", id)
    skynet.error("query sql:", sql)
    local res = db:query(sql)

    skynet.error("query_player_info query ret:", cjson.encode(res))
    if #res == 0 then
        return false, res
    end

    return true, res[1]
end

s.resp.check_username = function(source, username)
    local sql = string.format("select id from users where username=%s", mysql.quote_sql_str(username))
    skynet.error("check_username query sql:", sql)
    local res = db:query(sql)
    skynet.error("check_username query ret:", cjson.encode(res))
    return #res == 0
end

s.resp.register = function(source, username, password)
    local timestamps = os.time()
    local sql = string.format("insert into users(username, password, created, updated) values(%s, %s, %d, %d)",
        mysql.quote_sql_str(username), mysql.quote_sql_str(password), timestamps, timestamps)

    skynet.error("register query sql:", sql)
    local res = db:query(sql)

    skynet.error("register query ret:", cjson.encode(res))

    if res.err then
        skynet.error("register error:", res.err)
        return false
    else
        return true, res.insert_id
    end
end

s.resp.update_exp = function(source, id, data)
    local timestamps = os.time()
    local sql = string.format("update users set exp=%d where id=%d", data.coin or 0, id)
    skynet.error("update_exp query sql:", sql)

    local res = db:query(sql)

    skynet.error("update_exp query ret:", cjson.encode(res))

    if res.err then
        skynet.error("update_exp error:", res.err)
        return false
    else
        return true
    end
end

s.init = function()
    local function on_connected(db)
        skynet.error("mysql connected!")
    end

    local ok
    ok, db = pcall(mysql.connect, {
        host = mysql_host,
        port = mysql_port,
        database = mysql_database,
        user = mysql_username,
        password = mysql_password,
        max_packet_size = 1024 * 1024,
        on_connect = on_connected
    })

    if not ok or not db then
        skynet.error("connect to mysql failed")
    end

    -- keep mysql alive
    skynet.fork(function()
        while true do
            if db and db ~= socketchannel.error then
                local sql = string.format("select %s", skynet.time())
                skynet.error("keep mysql alive, start query")
                db:query(sql)
            end

            -- sleep 60 seconds
            skynet.sleep(6000)
        end
    end)
end

s.start(...)
